﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.Objects;
using System.Threading;

namespace XBoshiGFS
{
    
    
    
    class ImportJDQGData
    {
        XBoshiGFSModelContainer context = new XBoshiGFSModelContainer();

        public String ConnectionString {get;set ;}

        public bool IsFinished { get; set; }

        public bool IsClearData { get; set; }


        OleDbConnection objConnection ;


        private String currentStep = "";


        public void ImportStation() {
            objConnection.Open();  //打开连接 

            OleDbCommand sqlcmd = new OleDbCommand(@"select count(*) from tbzhan ", objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   
            reader.Read();
            long RecordCOunt = (long)reader.GetInt32(0);
            reader.Close(); 



             sqlcmd = new OleDbCommand(@"select * from tbzhan ",objConnection);  //sql语句   
             reader = sqlcmd.ExecuteReader();              //执行查询   
           
           while(reader.Read()){ //这个read调用很重要！不写的话运行时将提示找不到数据   
                   
               String strID = reader["IDZhan"].ToString() ;
               String strCode =  reader["IDZhan"].ToString();
               String strName = (String)reader["strName"];
               String strPhone = (String)reader["strPhone"];
               String strManager = (String)reader["strManager"];
               String strOther = (String)reader["strOther"];

               StationSet station = new StationSet(){
                Id=strID ,strCode =strCode ,strManager =strManager ,strName =strName ,strOther =strOther 
               };
               context.AddToStationSet(station);

           }
           context.SaveChanges() ; 
           objConnection.Close();
           reader.Close();  
        }

        public void ImportFeeStard()
        {
            objConnection.Open();  //打开连接   
            OleDbCommand sqlcmd = new OleDbCommand(@"select * from cdSJ ", objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   

            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据   
                try
                {
                    String strID = reader["IDType"].ToString();
                    String strCode = reader["IDType"].ToString();
                    String strName = reader["strName"].ToString();
                    Decimal PriceNational = Decimal.Parse(reader["mnyUnitPrice_N"].ToString());
                    Decimal PriceBase = Decimal.Parse(reader["mnyUnitPrice_B"].ToString());
                    Decimal PriceGroup = Decimal.Parse(reader["mnyUnitPrice_G"].ToString());
                    Decimal decPriceAll = Decimal.Parse(reader["mnyDJ"].ToString());
                    String strZhanID = reader["IDZhan"].ToString();

                    FeeStandardSet fee = new FeeStandardSet();
                    context.AddToFeeStandardSet(fee);

                    fee.Id = strID;
                    fee.strCode = strCode;
                    fee.PriceAll = decPriceAll;
                    fee.PriceNational  = PriceNational;
                    fee.PriceBase = PriceBase;
                    fee.PriceGroup = PriceGroup;
                    fee.strName = strName;
                    //segment.StationSet = (from p in context.StationSet where p.strCode == strZhanID select p).FirstOrDefault();
                    fee.Station_Id  = strZhanID;
                    context.SaveChanges();
                    fee = null;
                }
                catch (Exception ex)
                {
                }
            }

            objConnection.Close();
            reader.Close();
        }
        public void ImportSegment()
        {
            objConnection.Open();  //打开连接   
            OleDbCommand sqlcmd = new OleDbCommand(@"select * from tbzhi ", objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   

            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据   
                try
                {
                    String strID = reader["IDZhi"].ToString();
                    String strCode = reader["IDZhi"].ToString();
                    String strName = reader["strName"].ToString();
                    String strPhone = reader["strPhone"].ToString();
                    String strManager = reader["strManager"].ToString();
                    String strOther = reader["strOther"].ToString();
                    String strZhanID = reader["IDZhan"].ToString();


                    SegmentCourseSet segment = new SegmentCourseSet();
                    context.AddToSegmentCourseSet(segment);

                    segment.Id = strID;
                    segment.strCode = strCode;
                    segment.strManager = strManager;
                    segment.strName = strName;
                    segment.strOther = strOther;
                    //segment.StationSet = (from p in context.StationSet where p.strCode == strZhanID select p).FirstOrDefault();
                    segment.StationId  = strZhanID;
                    context.SaveChanges();
                    context.SegmentCourseSet.Detach(segment);   
                    segment = null;
 
                }catch (Exception ex){
                    currentStep = ex.InnerException.ToString() ;
                
                }
            }

            objConnection.Close();
            reader.Close();
        }

        public void ImportWaterCourse()
        {
            objConnection.Open();  //打开连接   
            OleDbCommand sqlcmd = new OleDbCommand(@"select * from tbdou ", objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   

            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据   
                try{
                String strID = reader["IDDou"].ToString();
                String strCode = reader["IDDou"].ToString();
                String strName = reader["strName"].ToString();
                String strPhone = reader["strPhone"].ToString();
                String strManager = reader["strManager"].ToString();
                String strOther = reader["strOther"].ToString();
                String strZhanID = reader["IDZhi"].ToString();


                WaterCourseSet segment = new WaterCourseSet();
                context.AddToWaterCourseSet(segment);

                segment.Id = strID;
                segment.strCode = strCode;
                segment.strManager = strManager;
                segment.strName = strName;
                segment.strOther = strOther;
            //    segment.SegmentCourseSet = (from p in context.SegmentCourseSet  where p.strCode == strZhanID select p).FirstOrDefault();
                segment.SegmentCourseId = strZhanID; 
                context.SaveChanges();
                context.WaterCourseSet.Detach(segment);   
                segment = null;

                 }catch (Exception ex){
                     currentStep = ex.InnerException.ToString();   
                }
            }

            objConnection.Close();
            reader.Close();
        }

        public void ImportVillage()
        {
            objConnection.Open();  //打开连接   
            OleDbCommand sqlcmd = new OleDbCommand(@"select * from tbcun ", objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   

            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据   
                try
                {
                    String strID = reader["IDCun"].ToString();
                    String strCode = reader["IDCun"].ToString();
                    String strName = reader["strName"].ToString();
                    String strPhone = reader["strPhone"].ToString();
                    String strManager = reader["strManager"].ToString();
                    String strOther = reader["strOther"].ToString();
                    String strDouID = reader["IDDou"].ToString();
                    String strZhanID = reader["IDZhan"].ToString();

                    VillageSet segment = new VillageSet();
                    context.AddToVillageSet(segment);

                    segment.Id = strID;
                    segment.strCode = strCode;
                  //  segment.strManager = strManager;
                    segment.strName = strName;
                   // segment.strOther = strOther;
                    //    segment.SegmentCourseSet = (from p in context.SegmentCourseSet  where p.strCode == strZhanID select p).FirstOrDefault();
                    segment.WaterCourseId = strDouID;
                    segment.StationId = strZhanID;
                    context.SaveChanges();
                    context.VillageSet.Detach(segment);
                    segment = null;
                   }
                catch (Exception ex)
                {

                    currentStep = ex.InnerException.ToString();
                }
            }

            objConnection.Close();
            reader.Close();
        }


        public void ImportGroup()
        {
            objConnection.Open();  //打开连接   
            OleDbCommand sqlcmd = new OleDbCommand(@"select * from tbzu ", objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   

            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据   
                try
                {
                    String strID = reader["IDZU"].ToString();
                    String strCode = reader["IDZU"].ToString();
                    String strName = reader["strName"].ToString();
                    String strPhone = reader["strPhone"].ToString();
                    String strManager = reader["strManager"].ToString();
                    String strOther = reader["strOther"].ToString();
                    String strDouID = reader["IDCun"].ToString();
                    String strZhanID = reader["IDZhan"].ToString();

                    GroupSet segment = new GroupSet();
                    context.AddToGroupSet(segment);

                    segment.Id = strID;
                    segment.strCode = strCode;
                    segment.strManager = strManager;
                    segment.strName = strName;
                    segment.strOther = strOther;
                    //    segment.SegmentCourseSet = (from p in context.SegmentCourseSet  where p.strCode == strZhanID select p).FirstOrDefault();
                    segment.VillageId  = strDouID;
                    context.SaveChanges();
                    context.GroupSet.Detach(segment);
                    segment = null;
                }
                catch (Exception ex)
                {
                    currentStep = ex.InnerException.ToString();

                }
            }

            objConnection.Close();
            reader.Close();
        }

        public void ImportPeople()
        {
            objConnection.Open();  //打开连接   

            OleDbCommand sqlcmd = new OleDbCommand(@"select count(*) from tbYSR ", objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   
            reader.Read();
            long RecordCOunt = (long)reader.GetInt32(0);
            reader.Close(); 
            
            
            sqlcmd = new OleDbCommand(@"select * from tbYSR ", objConnection);  //sql语句   
            reader = sqlcmd.ExecuteReader();              //执行查询   
            var j=0;
            var k = 0;
             
            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据   
                try
                {
                    String strID = Guid.NewGuid().ToString();
                    String strCode = reader["IDYSR"].ToString();
                    String strName = reader["strName"].ToString();
                    //String strPhone = reader["strPhone"].ToString();
                    //String strManager = reader["strManager"].ToString();
                    String strOther = reader["strOther"].ToString();
                    String strAddr = reader["strAddr"].ToString();

                    String strZuID = reader["IDZu"].ToString();
                    String strDouID = reader["IDDou"].ToString();

                    PeopleSet segment = new PeopleSet();
                    context.AddToPeopleSet(segment);

                    segment.Id = strID;
                    segment.strCode = strCode;
                    //  segment.strManager = strManager;
                    segment.strName = strName;
                    segment.OrginID = reader["IDYSR"].ToString();
                    // segment.strOther = strOther;
                    //    segment.SegmentCourseSet = (from p in context.SegmentCourseSet  where p.strCode == strZhanID select p).FirstOrDefault();
                    segment.GroupId = strZuID;
                    segment.WaterCourseId = strDouID;
                    if (k >= 1000)
                    {
                        context.SaveChanges();

                        k = 0;
                        currentStep = String.Format("用水户数据导入完成{0}000条/{1}", j, RecordCOunt);
                        j = j + 1;
                        Thread.Sleep(0);  
                    }
                    else {
                        k = k + 1;
                    
                    }
                    //context.PeopleSet.Detach(segment);
                    //segment = null;
                }
                catch (Exception ex)
                {
                    currentStep = ex.InnerException.ToString();

                }

            }
            context.SaveChanges();
            objConnection.Close();
            reader.Close();
        }

        /// <summary>
        /// 导入用水记录
        /// </summary>
        public void ImportWaterUsage()
        {
            objConnection.Open();  //打开连接   

            OleDbCommand sqlcmd = new OleDbCommand(@"select count(*) from tbYSJL ", objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   
            reader.Read();
            long RecordCOunt = (long)reader.GetInt32(0);
            reader.Close(); 



              sqlcmd = new OleDbCommand(@"select * from tbYSJL order by IDZhan,IDZhi,IDDou,IDCun,IDZu,IDYSR ", objConnection);  //sql语句   
              reader = sqlcmd.ExecuteReader();              //执行查询   

            var k = 0;
            var j = 0;
            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据   
                try
                {
                    String strID = Guid.NewGuid().ToString();
                    //确定用水人
                    String strCode = reader["IDYSR"].ToString();
                    String strZuID = reader["IDZu"].ToString();
                    PeopleSet  people = (from p in context.PeopleSet where p.GroupId == strZuID & p.OrginID == strCode select p).FirstOrDefault();



                    //确定灌季
                    String strYear = reader["sintGJN"].ToString();
                    String strGJ = reader["sintGJ"].ToString();
                    WaterSeasonSet season = (from p in context.WaterSeasonSet where p.strYear == strYear & p.strSeason == strGJ select p).FirstOrDefault();



                    //确定费用标准
                    String strFeeStand = reader["IDType"].ToString();




                    //用水信息
                    DateTime  strBeginTime =DateTime.Parse( reader["dtFSRQ"].ToString()); //开始用水时间
                    decimal  strUseLong =Decimal.Parse(reader["fltSJ"].ToString());//用水时长
                    decimal strFlowRate = Decimal.Parse(reader["fltLL"].ToString());
                    decimal strUseArea = Decimal.Parse(reader["fltMS"].ToString());
                    decimal strWaterBill = Decimal.Parse(reader["mnySF"].ToString());

                    //开票标志
                    String strRecState = reader["sintRecState"].ToString();
                    bool isFeePrinLog = false;
                    if (strRecState == "9") { 
                        //已经开票
                        isFeePrinLog = true;
                    }

                    //收费标志



                    WaterUsageSet wus = new WaterUsageSet();
                    context.AddToWaterUsageSet(wus);

                    wus.Id = strID;
                    wus.PeopleSet = people;
                    wus.FeeStandardId = strFeeStand;
                    wus.WaterSeasonSet = season;
                    wus.UseArea = strUseArea;
                    wus.UseLong = strUseLong;
                    wus.WaterBill = strWaterBill;
                    wus.BeginTime = strBeginTime;
                    wus.FlowRate = strFlowRate;
                    wus.WaterCap = strFlowRate * strUseLong*60/1000;

                    //发票打印
                    if (isFeePrinLog) { 
                        FeePrintLogSet fls=new FeePrintLogSet ();
                        fls.Id=Guid.NewGuid().ToString ();
                        fls.WaterUsageId = strID;
                        fls.PrintTime = DateTime.Now;
                        context.AddToFeePrintLogSet(fls);  
                    }

                    //收费数据



                    if (k >= 1000)
                    {
                        context.SaveChanges();

                        currentStep = String.Format("用水数据导入完成{0}000条/{1}", j, RecordCOunt);
                        j = j + 1;
                        Thread.Sleep(0);
                        k = 0;
                    }
                    else
                    {
                        k = k + 1;

                    }
                    //context.PeopleSet.Detach(segment);
                    //segment = null;
                }
                catch (Exception ex)
                {


                }
    
            }
            context.SaveChanges();
            objConnection.Close();
            reader.Close();
        }


        /// <summary>
        /// 导入灌季
        /// </summary>
        public void ImportWaterSeason()
        {
            objConnection.Open();  //打开连接   
            OleDbCommand sqlcmd = new OleDbCommand(@"select distinct sintGJN,sintGJ from TBYSJL ", objConnection);  //sql语句   
            OleDbDataReader reader = sqlcmd.ExecuteReader();              //执行查询   

            while (reader.Read())
            { //这个read调用很重要！不写的话运行时将提示找不到数据   
                try
                {
                    String strID = Guid.NewGuid().ToString() ;
                    String strYear = reader["sintGJN"].ToString();
                    String strName ="";
                    String strSeason = reader["sintGJ"].ToString();
                    String strFeeStandardId = "1"; // reader["IDType"].ToString();//默认库水
                    switch (strSeason)
                    {
                        case "1":
                            strName = strYear+ "年春灌";
                            break;
                        case "2":
                            strName = strYear + "年夏灌";
                            break;
                        case "3":
                            strName = strYear + "年秋灌";
                            break;
                        case "4":
                            strName = strYear + "年冬灌";
                            break;
                        default :
                            strName = strYear + "年灌季";
                            break;
                    }



                    WaterSeasonSet season = new WaterSeasonSet();
                    
                    context.AddToWaterSeasonSet(season);
                    

                    season.Id = strID;
                    season.strYear = strYear;
                    season.strSeason = strSeason; 
                    season.strName = strName;
                    season.FeeStandardSetId = strFeeStandardId;
   
                    context.SaveChanges();
    
                    season = null;
                }
                catch (Exception ex)
                {


                }
            }

            objConnection.Close();
            reader.Close();
        }


        public void buidConnection() {

            objConnection = new OleDbConnection(ConnectionString);
        }

        public void ClearDataBase(){
         }


        public void BeginImport(){

            buidConnection();

            if (IsClearData)
            {
                ClearDataBase();
            }

            currentStep =String.Format("{0}开始导入数据：",DateTime.Now); 

            ImportStation();
            currentStep = "管理站数据导入完成！"; 

            ImportFeeStard();
            currentStep = "收费标准数据导入完成"; 

            ImportWaterSeason();
            currentStep = "灌季数据导入完成"; 


            ImportSegment();
            currentStep = "支渠数据导入完成"; 

            ImportWaterCourse();
            currentStep = "斗渠数据导入完成"; 


            ImportVillage();
            currentStep = "村数据导入完成"; 

            ImportGroup();
            currentStep = "组数据导入完成"; 

            ImportPeople();
            currentStep = "用水户数据导入完成"; 

            ImportWaterUsage();
            currentStep = "用水数据导入完成"; 


            IsFinished = true;
        }

        public String  GetCurrentStep() {

            return currentStep;
        }
    }
}
